
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 05/23/2014 12:49:46
-- Generated from EDMX file: C:\work\net\akz\AkzoNobel.MDC\H2.NET.Web\BI\Model\MDC.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [UltimusMDC3];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_bi_Column_bi_table]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[bi_Column] DROP CONSTRAINT [FK_bi_Column_bi_table];
GO
IF OBJECT_ID(N'[dbo].[FK_bi_Relation_bi_Column]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[bi_Relation] DROP CONSTRAINT [FK_bi_Relation_bi_Column];
GO
IF OBJECT_ID(N'[dbo].[FK_bi_Relation_bi_Column1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[bi_Relation] DROP CONSTRAINT [FK_bi_Relation_bi_Column1];
GO
IF OBJECT_ID(N'[dbo].[FK_bi_TemplateDetail_bi_Column]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[bi_TemplateDetail] DROP CONSTRAINT [FK_bi_TemplateDetail_bi_Column];
GO
IF OBJECT_ID(N'[dbo].[FK_bi_Table_bi_DB]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[bi_Table] DROP CONSTRAINT [FK_bi_Table_bi_DB];
GO
IF OBJECT_ID(N'[dbo].[FK_bi_Relation_bi_Table]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[bi_Relation] DROP CONSTRAINT [FK_bi_Relation_bi_Table];
GO
IF OBJECT_ID(N'[dbo].[FK_bi_Relation_bi_Table1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[bi_Relation] DROP CONSTRAINT [FK_bi_Relation_bi_Table1];
GO
IF OBJECT_ID(N'[dbo].[FK_bi_TemplateDetail_bi_Template]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[bi_TemplateDetail] DROP CONSTRAINT [FK_bi_TemplateDetail_bi_Template];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[bi_Column]', 'U') IS NOT NULL
    DROP TABLE [dbo].[bi_Column];
GO
IF OBJECT_ID(N'[dbo].[bi_DB]', 'U') IS NOT NULL
    DROP TABLE [dbo].[bi_DB];
GO
IF OBJECT_ID(N'[dbo].[bi_Relation]', 'U') IS NOT NULL
    DROP TABLE [dbo].[bi_Relation];
GO
IF OBJECT_ID(N'[dbo].[bi_Table]', 'U') IS NOT NULL
    DROP TABLE [dbo].[bi_Table];
GO
IF OBJECT_ID(N'[dbo].[bi_Template]', 'U') IS NOT NULL
    DROP TABLE [dbo].[bi_Template];
GO
IF OBJECT_ID(N'[dbo].[bi_TemplateDetail]', 'U') IS NOT NULL
    DROP TABLE [dbo].[bi_TemplateDetail];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'bi_Column'
CREATE TABLE [dbo].[bi_Column] (
    [id] int IDENTITY(1,1) NOT NULL,
    [table_id] int  NOT NULL,
    [column_name] nvarchar(50)  NOT NULL,
    [type] nvarchar(50)  NOT NULL,
    [length] int  NULL,
    [format] nvarchar(100)  NULL,
    [name_cn] nvarchar(50)  NOT NULL,
    [name_en] nvarchar(50)  NULL,
    [remark] nvarchar(200)  NULL
);
GO

-- Creating table 'bi_DB'
CREATE TABLE [dbo].[bi_DB] (
    [id] int IDENTITY(1,1) NOT NULL,
    [name] nvarchar(50)  NOT NULL,
    [remark] nvarchar(200)  NULL
);
GO

-- Creating table 'bi_Relation'
CREATE TABLE [dbo].[bi_Relation] (
    [id] int IDENTITY(1,1) NOT NULL,
    [tableA_id] int  NOT NULL,
    [tableB_id] int  NOT NULL,
    [columnA_id] int  NOT NULL,
    [columnB_id] int  NOT NULL
);
GO

-- Creating table 'bi_Table'
CREATE TABLE [dbo].[bi_Table] (
    [id] int IDENTITY(1,1) NOT NULL,
    [db_id] int  NOT NULL,
    [table_name] nvarchar(50)  NOT NULL,
    [name_cn] nvarchar(50)  NOT NULL,
    [name_en] nvarchar(50)  NULL,
    [remark] nvarchar(200)  NULL
);
GO

-- Creating table 'bi_Template'
CREATE TABLE [dbo].[bi_Template] (
    [id] int IDENTITY(1,1) NOT NULL,
    [name] nvarchar(100)  NOT NULL,
    [create_user] nvarchar(50)  NOT NULL,
    [create_date] datetime  NOT NULL,
    [sql] varchar(max)  NOT NULL,
    [permission] nvarchar(500)  NULL,
    [pub] bit  NULL
);
GO

-- Creating table 'bi_TemplateDetail'
CREATE TABLE [dbo].[bi_TemplateDetail] (
    [id] int IDENTITY(1,1) NOT NULL,
    [template_id] int  NOT NULL,
    [column_id] int  NOT NULL,
    [sort] nvarchar(10)  NULL,
    [sum] bit  NULL,
    [filter] bit  NULL,
    [width] float  NULL,
    [type] nvarchar(10)  NULL,
    [value] nvarchar(500)  NULL,
    [fix] bit  NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [id] in table 'bi_Column'
ALTER TABLE [dbo].[bi_Column]
ADD CONSTRAINT [PK_bi_Column]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [id] in table 'bi_DB'
ALTER TABLE [dbo].[bi_DB]
ADD CONSTRAINT [PK_bi_DB]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [id] in table 'bi_Relation'
ALTER TABLE [dbo].[bi_Relation]
ADD CONSTRAINT [PK_bi_Relation]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [id] in table 'bi_Table'
ALTER TABLE [dbo].[bi_Table]
ADD CONSTRAINT [PK_bi_Table]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [id] in table 'bi_Template'
ALTER TABLE [dbo].[bi_Template]
ADD CONSTRAINT [PK_bi_Template]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [id] in table 'bi_TemplateDetail'
ALTER TABLE [dbo].[bi_TemplateDetail]
ADD CONSTRAINT [PK_bi_TemplateDetail]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [table_id] in table 'bi_Column'
ALTER TABLE [dbo].[bi_Column]
ADD CONSTRAINT [FK_bi_Column_bi_table]
    FOREIGN KEY ([table_id])
    REFERENCES [dbo].[bi_Table]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_bi_Column_bi_table'
CREATE INDEX [IX_FK_bi_Column_bi_table]
ON [dbo].[bi_Column]
    ([table_id]);
GO

-- Creating foreign key on [columnA_id] in table 'bi_Relation'
ALTER TABLE [dbo].[bi_Relation]
ADD CONSTRAINT [FK_bi_Relation_bi_Column]
    FOREIGN KEY ([columnA_id])
    REFERENCES [dbo].[bi_Column]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_bi_Relation_bi_Column'
CREATE INDEX [IX_FK_bi_Relation_bi_Column]
ON [dbo].[bi_Relation]
    ([columnA_id]);
GO

-- Creating foreign key on [columnB_id] in table 'bi_Relation'
ALTER TABLE [dbo].[bi_Relation]
ADD CONSTRAINT [FK_bi_Relation_bi_Column1]
    FOREIGN KEY ([columnB_id])
    REFERENCES [dbo].[bi_Column]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_bi_Relation_bi_Column1'
CREATE INDEX [IX_FK_bi_Relation_bi_Column1]
ON [dbo].[bi_Relation]
    ([columnB_id]);
GO

-- Creating foreign key on [column_id] in table 'bi_TemplateDetail'
ALTER TABLE [dbo].[bi_TemplateDetail]
ADD CONSTRAINT [FK_bi_TemplateDetail_bi_Column]
    FOREIGN KEY ([column_id])
    REFERENCES [dbo].[bi_Column]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_bi_TemplateDetail_bi_Column'
CREATE INDEX [IX_FK_bi_TemplateDetail_bi_Column]
ON [dbo].[bi_TemplateDetail]
    ([column_id]);
GO

-- Creating foreign key on [db_id] in table 'bi_Table'
ALTER TABLE [dbo].[bi_Table]
ADD CONSTRAINT [FK_bi_Table_bi_DB]
    FOREIGN KEY ([db_id])
    REFERENCES [dbo].[bi_DB]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_bi_Table_bi_DB'
CREATE INDEX [IX_FK_bi_Table_bi_DB]
ON [dbo].[bi_Table]
    ([db_id]);
GO

-- Creating foreign key on [tableA_id] in table 'bi_Relation'
ALTER TABLE [dbo].[bi_Relation]
ADD CONSTRAINT [FK_bi_Relation_bi_Table]
    FOREIGN KEY ([tableA_id])
    REFERENCES [dbo].[bi_Table]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_bi_Relation_bi_Table'
CREATE INDEX [IX_FK_bi_Relation_bi_Table]
ON [dbo].[bi_Relation]
    ([tableA_id]);
GO

-- Creating foreign key on [tableB_id] in table 'bi_Relation'
ALTER TABLE [dbo].[bi_Relation]
ADD CONSTRAINT [FK_bi_Relation_bi_Table1]
    FOREIGN KEY ([tableB_id])
    REFERENCES [dbo].[bi_Table]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_bi_Relation_bi_Table1'
CREATE INDEX [IX_FK_bi_Relation_bi_Table1]
ON [dbo].[bi_Relation]
    ([tableB_id]);
GO

-- Creating foreign key on [template_id] in table 'bi_TemplateDetail'
ALTER TABLE [dbo].[bi_TemplateDetail]
ADD CONSTRAINT [FK_bi_TemplateDetail_bi_Template]
    FOREIGN KEY ([template_id])
    REFERENCES [dbo].[bi_Template]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_bi_TemplateDetail_bi_Template'
CREATE INDEX [IX_FK_bi_TemplateDetail_bi_Template]
ON [dbo].[bi_TemplateDetail]
    ([template_id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------